package com.efesco.stat.operation;

import com.bstek.dorado.data.*;
import com.bstek.dorado.data.db.SqlDataset;
import com.bstek.dorado.common.*;
import com.efesco.common.Tools;
import com.efesco.system.User;

/**
 * achievementRate_oughtDatasetListener
 */
public class achievementRate_oughtDatasetListener extends AbstractDatasetListener {
  public boolean beforeLoadData(Dataset dataset)
     throws Exception {
	    String sql="",foreign_enterprise_id="";
	  	
	  	DoradoContext context = DoradoContext.getContext();
	    Object obj = context.getAttribute(context.SESSION, "User");
	    if (obj != null) {
	    	User user = (User) obj;
    		foreign_enterprise_id=user.getForeignEnterpriseId();
	    	String startDate = Tools.format(dataset.parameters().getDate("startDate"),"yyyy-MM-dd");
	    	String endDate = Tools.format(dataset.parameters().getDate("endDate"),"yyyy-MM-dd");
	    	String mainPart = dataset.parameters().getString("mainPart");
	    	if (dataset.parameters().getString("statItem").equals("1"))  {
	    		sql="select distinct cmp.company_code,cmp.company_chinese_name,emp.employee_code,emp.employee_name,eoop.on_post_date," +
	    				"st.create_date apply_date " +
	    				"from s_in_char_service_transaction st ,s_in_char_list il,s_employee_on_off_post eoop,s_employee emp,s_single_company cmp,s_in_char_service cs " +
	    				"where  st.in_char_id=il.in_char_id and il.employee_on_off_id=eoop.employee_on_off_id " +
	    				"and eoop.employee_id=emp.employee_id and eoop.single_company_id=cmp.single_company_id " +
	    				"and st.in_char_service_id = cs.in_char_service_id and cs.service_type=33 and st.status<>'3' " +
	    				"and cmp.foreign_enterprise_id="+foreign_enterprise_id;
	    		if (!startDate.equals(""))  {
	    			sql = sql + " and st.create_date >= to_date('"+startDate+"','yyyy-mm-dd')";
	    		}
	    		if (!endDate.equals(""))  {
	    			sql = sql + " and st.create_date <= to_date('"+endDate+"','yyyy-mm-dd')";
	    		}
	    		if (!mainPart.equals(""))  {
	    			sql = sql + " and cmp.main_part = "+mainPart;
	    		}
	    	}
	    	if (dataset.parameters().getString("statItem").equals("2"))  {
	    		sql="select distinct cmp.company_code,cmp.company_chinese_name,emp.employee_code,emp.employee_name,eoop.on_post_date," +
	    				"lc.input_date apply_date " +
	    				"from s_labor_contract lc,s_single_company cmp,s_employee emp,s_employee_on_off_post eoop " +
	    				"where lc.employee_id=emp.employee_id and lc.single_company_id=cmp.single_company_id " +
	    				"and lc.employee_on_off_id=eoop.employee_on_off_id and lc.status < 3 " +
	    				"and cmp.foreign_enterprise_id="+foreign_enterprise_id;
	    		if (!startDate.equals(""))  {
	    			sql = sql + " and lc.input_date >= to_date('"+startDate+"','yyyy-mm-dd')";
	    		}
	    		if (!endDate.equals(""))  {
	    			sql = sql + " and lc.input_date <= to_date('"+endDate+"','yyyy-mm-dd')";
	    		}
	    		if (!mainPart.equals(""))  {
	    			sql = sql + " and cmp.main_part = "+mainPart;
	    		}
	    	}
	    	if (dataset.parameters().getString("statItem").equals("3"))  {
	    		sql="select distinct cmp.company_code,cmp.company_chinese_name,emp.employee_code,emp.employee_name,eoop.on_post_date," +
	    				"ann.apply_date apply_date " +
	    				"from s_in_char_service_transaction st ,s_in_char_list il,s_annuity ann,s_employee_on_off_post eoop," +
	    				"s_employee emp,s_single_company cmp,s_in_char_service cs " +
	    				"where  st.in_char_id=il.in_char_id and il.employee_on_off_id=eoop.employee_on_off_id " +
	    				"and eoop.employee_on_off_id=ann.employee_on_off_id and eoop.employee_id=emp.employee_id " +
	    				"and eoop.single_company_id=cmp.single_company_id and st.in_char_service_id=cs.in_char_service_id " +
	    				"and cs.service_type=1 and st.status<>'0' and cmp.foreign_enterprise_id="+foreign_enterprise_id;
	    		if (!startDate.equals(""))  {
	    			sql = sql + " and ann.apply_date >= to_date('"+startDate+"','yyyy-mm-dd')";
	    		}
	    		if (!endDate.equals(""))  {
	    			sql = sql + " and ann.apply_date <= to_date('"+endDate+"','yyyy-mm-dd')";
	    		}
	    		if (!mainPart.equals(""))  {
	    			sql = sql + " and cmp.main_part = "+mainPart;
	    		}
	    	}
	    	if (dataset.parameters().getString("statItem").equals("4"))  {
	    		sql="select distinct cmp.company_code,cmp.company_chinese_name,emp.employee_code,emp.employee_name,eoop.on_post_date," +
	    				"zm.apply_date apply_date " +
	    				"from s_in_char_service_transaction st ,s_in_char_list il,s_zhonghe_manage zm," +
	    				"s_employee_on_off_post eoop,s_employee emp,s_single_company cmp,s_in_char_service cs " +
	    				"where  st.in_char_id=il.in_char_id and il.employee_on_off_id=eoop.employee_on_off_id " +
	    				"and eoop.employee_on_off_id=zm.employee_on_off_id and eoop.employee_id=emp.employee_id " +
	    				"and eoop.single_company_id=cmp.single_company_id and st.in_char_service_id=cs.in_char_service_id " +
	    				"and cs.service_type=30 and st.status<>'0' and cmp.foreign_enterprise_id="+foreign_enterprise_id;
	    		if (!startDate.equals(""))  {
	    			sql = sql + " and zm.apply_date >= to_date('"+startDate+"','yyyy-mm-dd')";
	    		}
	    		if (!endDate.equals(""))  {
	    			sql = sql + " and zm.apply_date <= to_date('"+endDate+"','yyyy-mm-dd')";
	    		}
	    		if (!mainPart.equals(""))  {
	    			sql = sql + " and cmp.main_part = "+mainPart;
	    		}
	    	}
	    	if (dataset.parameters().getString("statItem").equals("5"))  {
	    		sql="select distinct cmp.company_code,cmp.company_chinese_name,emp.employee_code,emp.employee_name,eoop.on_post_date," +
	    				"acc.apply_date apply_date " +
	    				"from s_in_char_service_transaction st ,s_in_char_list il,s_accumulation acc," +
	    				"s_employee_on_off_post eoop,s_employee emp,s_single_company cmp,s_in_char_service cs " +
	    				"where  st.in_char_id=il.in_char_id and il.employee_on_off_id=eoop.employee_on_off_id " +
	    				"and eoop.employee_on_off_id=acc.employee_on_off_id and eoop.employee_id=emp.employee_id " +
	    				"and eoop.single_company_id=cmp.single_company_id and st.in_char_service_id=cs.in_char_service_id " +
	    				"and cs.service_type=10 and st.status<>'0' and acc.accumulation_type=1  " +
	    				" and cmp.foreign_enterprise_id="+foreign_enterprise_id;
	    		if (!startDate.equals(""))  {
	    			sql = sql + " and acc.apply_date >= to_date('"+startDate+"','yyyy-mm-dd')";
	    		}
	    		if (!endDate.equals(""))  {
	    			sql = sql + " and acc.apply_date <= to_date('"+endDate+"','yyyy-mm-dd')";
	    		}
	    		if (!mainPart.equals(""))  {
	    			sql = sql + " and cmp.main_part = "+mainPart;
	    		}
	    	}
	    	if (dataset.parameters().getString("statItem").equals("6"))  {
	    		sql="select distinct cmp.company_code,cmp.company_chinese_name,emp.employee_code,emp.employee_name,eoop.on_post_date," +
						"acc.apply_date apply_date " +
						"from s_in_char_service_transaction st ,s_in_char_list il,s_accumulation acc," +
						"s_employee_on_off_post eoop,s_employee emp,s_single_company cmp,s_in_char_service cs " +
						"where  st.in_char_id=il.in_char_id and il.employee_on_off_id=eoop.employee_on_off_id " +
						"and eoop.employee_on_off_id=acc.employee_on_off_id and eoop.employee_id=emp.employee_id " +
						"and eoop.single_company_id=cmp.single_company_id and st.in_char_service_id=cs.in_char_service_id " +
						"and cs.service_type=11 and st.status<>'0' and acc.accumulation_type=2  " +
						" and cmp.foreign_enterprise_id="+foreign_enterprise_id;
	    		if (!startDate.equals(""))  {
	    			sql = sql + " and acc.apply_date >= to_date('"+startDate+"','yyyy-mm-dd')";
	    		}
	    		if (!endDate.equals(""))  {
	    			sql = sql + " and acc.apply_date <= to_date('"+endDate+"','yyyy-mm-dd')";
	    		}
	    		if (!mainPart.equals(""))  {
	    			sql = sql + " and cmp.main_part = "+mainPart;
	    		}
	    	}
	    	
	    	if (!sql.equals(""))  {
	    		sql = sql + " order by cmp.company_code,emp.employee_code ";
	    	}
	    	SqlDataset sqlDataset=(SqlDataset)dataset;
	    	sqlDataset.setSql(sql);
	    }
    return true;
  }

}
